import json, pymysql

# 准备插入的数据
data_list = []
count = 0
pid = 0
with open(file='init.json', mode='r', encoding='utf-8') as f:
    data = json.load(f)
    # print(data.get('menuInfo'))
    menuInfos = data.get('menuInfo')
    for menuInfo in menuInfos:
        pid += 1
        if None != menuInfo.get('child'):
            # 获取侧边一级菜单
            level_1 = menuInfo.get('child')
            for level_1_menu in level_1:
                data_list.append((pid, level_1_menu.get('title'), level_1_menu.get('icon'), level_1_menu.get('href'), level_1_menu.get('target'), count))
                if None != level_1_menu.get('child'):
                    # 获取侧边二级菜单
                    level_2 = level_1_menu.get('child')
                    for level_2_menu in level_2:
                        data_list.append((pid, level_2_menu.get('title'), level_2_menu.get('icon'), level_2_menu.get('href'), level_2_menu.get('target'), count))
for data in data_list:
    print(data)
# 连接数据库
conn = pymysql.Connect(
    # host='47.94.130.233',
    host='localhost',
    port=3306,
    user='root',
    passwd='123456',
    db='homemall',
    charset='utf8',
)
# 获取游标
my_cursor = conn.cursor()
# 准备SQL
sql = 'insert into system_menu (pid,title,icon,href,target,sort) values (%s,%s,%s,%s,%s,%s)'

try:
    # 批量插入数据
    my_cursor.executemany(sql, data_list)
    # 手动提交事务
    conn.commit()
    print('提交了', my_cursor.rowcount, '条数据')
except Exception as e:
    print(e)
    # 事务回滚
    conn.rollback()
finally:
    # 关闭连接
    conn.close()
print('写入成功')
